package bdsimple.impl.mysql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bdsimple.abstractfactory.AbstractFactoryDAO;
import bdsimple.dao.base.AvionDAO;
import bdsimple.dao.base.ItemDAO;
import bdsimple.dao.base.ProductoDAO;
import bdsimple.dto.base.AvionDTO;
import bdsimple.dto.base.ItemDTO;
import bdsimple.dto.base.PrecioItemCiudadDTO;
import bdsimple.dto.base.ProductoDTO;
import dao.base.api.IDAO;
import dao.base.api.IDTO;

public class AvionDAOImpl extends MySQLBaseDAO implements AvionDAO {

	public AvionDAOImpl() {
		super(AvionDTO.class);
		daoParentClass = ProductoDAO.class;
	}

	@Override
	protected String createInsertCollst(IDTO dto) throws Exception {

		StringBuffer strbuf = new StringBuffer();

		strbuf.append(AvionDTOImpl.ID);
		strbuf.append(",");
		strbuf.append(AvionDTOImpl.ATRIBUTO_VACIO);

		return strbuf.toString();
	}

	@Override
	protected String createInsertValues(IDTO dto) throws Exception {
		
		AvionDTOImpl avionDTOImpl = (AvionDTOImpl) dto;

	    StringBuffer strbuf = new StringBuffer();
	    
	    strbuf.append(avionDTOImpl.getId());
	    strbuf.append(",");
		strbuf.append(avionDTOImpl.getAtributoVacio());

	    return strbuf.toString();

	}

	@Override
	protected String createTableColumns() throws Exception {

		StringBuffer strbuf = new StringBuffer();

	    strbuf.append(AvionDTOImpl.ID);
	    strbuf.append(" INT PRIMARY KEY, ");
	    strbuf.append(AvionDTOImpl.ATRIBUTO_VACIO);
	    strbuf.append(" INT ");
	    
	    return strbuf.toString();

	}

	@Override
	protected String createUpdateValues(IDTO dto) throws Exception {

		StringBuffer strbuf = new StringBuffer();
		
		AvionDTOImpl avionDTOImpl = (AvionDTOImpl) dto; 
		
		strbuf.append(AvionDTOImpl.ATRIBUTO_VACIO);
	    strbuf.append(" = ");
	    strbuf.append(avionDTOImpl.getAtributoVacio());

		return strbuf.toString();

	}

	@Override
	protected AvionDTOImpl internalResultSetToDTO(ResultSet rs, IDTO dto)
			throws Exception {

		AvionDTOImpl ret = (AvionDTOImpl) dto;
		
		ret.setAtributoVacio(rs.getInt(AvionDTO.ATRIBUTO_VACIO));
		
	    return ret;

	}
	
	@Override
	public List<IDTO> listAll(int lim, int off) throws Exception {

	    StringBuffer strbuf = new StringBuffer();

	    IDAO par0 = //
	    AbstractFactoryDAO.getFactoryDAO().getDAO( //
	        daoParentClass, connectionBean);

	    strbuf.append("SELECT * FROM ");
	    strbuf.append(this.getTableName());
	    strbuf.append(", ");
	    strbuf.append(par0.getTableName());

	    strbuf.append(" WHERE ");
	    strbuf.append( //
	        /**/par0.getTableName() + "." + ProductoDTO.ID + //
	            " = " + //
	            this.getTableName() + "." + AvionDTO.ID);

	    if (lim >= 0 && off >= 0) {
	      strbuf.append(" LIMIT  ");
	      strbuf.append(lim);
	      strbuf.append(" OFFSET ");
	      strbuf.append(off);
	    }

	    System.err.println(strbuf.toString());

	    ResultSet rs = //
	    connection.createStatement().executeQuery(strbuf.toString());

	    List<IDTO> ret = new ArrayList<IDTO>();

	    while (rs.next()) {
	      ret.add(resultSetToDTO(rs));
	    }

	    return ret;
	}

	@Override
	public List<IDTO> listBy(String key, Object val) throws Exception {

		if (key == null || val == null) {
	      throw new IllegalArgumentException("key == null || val == null");
	    }

	    StringBuffer strbuf = new StringBuffer();

	    IDAO par0 = //
	    AbstractFactoryDAO.getFactoryDAO().getDAO( //
	        daoParentClass, connectionBean);

	    strbuf.append("SELECT * FROM ");
	    strbuf.append(this.getTableName());
	    strbuf.append(", ");
	    strbuf.append(par0.getTableName());

	    strbuf.append(" WHERE ");
	    strbuf.append(key);
	    strbuf.append(" = ");
	    strbuf.append(val);

	    strbuf.append(" AND ");
	    strbuf.append( //
	        /**/par0.getTableName() + "." + ProductoDTO.ID + //
	            " = " + //
	            this.getTableName() + "." + AvionDTO.ID);

	    System.err.println(strbuf.toString());

	    ResultSet rs = //
	    connection.createStatement().executeQuery(strbuf.toString());

	    List<IDTO> ret = new ArrayList<IDTO>();

	    while (rs.next()) {
	      ret.add(resultSetToDTO(rs));
	    }

	    return ret;
	}
	
	public byte[] loadImageByItemId(int articuloId) throws SQLException {
        StringBuffer strbuf = new StringBuffer();

        strbuf.append("SELECT ");
        strbuf.append(ItemDTO.IMAGEN);
        strbuf.append(" FROM ");
        strbuf.append(getTableName());
        strbuf.append(" WHERE ");
        strbuf.append(ItemDTO.ID);
        strbuf.append(" = ");
        strbuf.append(articuloId);

        System.err.println(strbuf.toString());

        ResultSet rs = //
        connection.createStatement().executeQuery(strbuf.toString());

        if (rs.next()) {
            return rs.getBytes(ItemDTO.IMAGEN);
        }

        return null;
    }
	
	public List<ItemDTO> listByCiudadId(int id) throws Exception { 
        StringBuffer strbuf = new StringBuffer();

        PrecioItemCiudadDAOImpl ciudadArticuloDAO = new PrecioItemCiudadDAOImpl();
        ItemDAO articuloDAOImpl = new ItemDAOImpl();

        strbuf.append("SELECT " + getTableName() + ".* FROM ");
        strbuf.append(getTableName());
        strbuf.append(" JOIN " + ciudadArticuloDAO.getTableName());
        strbuf.append(" ON " + articuloDAOImpl.getTableName() + "." + ItemDTO.ID + " = " + PrecioItemCiudadDTO.ITEM_REFERENCIA);
        strbuf.append(" WHERE " + PrecioItemCiudadDTO.CIUDAD_REFERENCIA + " = " + id);

        System.err.println(strbuf.toString());

        ResultSet rs = //
        connection.createStatement().executeQuery(strbuf.toString());

        List<ItemDTO> ret = new ArrayList<ItemDTO>();

        while (rs.next()) {
            ret.add((ItemDTO) resultSetToDTO(rs));
        }

        return ret;
    }
}
